﻿using OfficeHelper.Model.SQLGenerate;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OfficeHelper.Help
{

    /// <summary>
    /// Sql生成猪油
    /// </summary>
    public class SQLServerSQLHelp
    {
        /// <summary>
        /// 创建表格
        /// </summary>
        /// <param name="sQLGenerateFields"></param>
        /// <returns></returns>
        public static string CreateTable(ICollection<SQLGenerateField> sQLGenerateFields,string TableName )
        {

            /*   CREATE TABLE[dbo].[NewTable]
           (
   [ID] varchar(64) COLLATE Chinese_PRC_CI_AS NOT NULL,
   [StatisticsDate] datetime NULL,
   [CoalID] varchar(64) COLLATE Chinese_PRC_CI_AS NULL ,
   [CoalName] varchar(64) COLLATE Chinese_PRC_CI_AS NULL ,
   [WashState] varchar(64) COLLATE Chinese_PRC_CI_AS NULL ,
   [CleanTotal] numeric(18) NULL ,
   [DeptID] varchar(64) COLLATE Chinese_PRC_CI_AS NULL ,
   [CreatorID] varchar(64) COLLATE Chinese_PRC_CI_AS NULL ,
   [CreateTime] datetime NULL DEFAULT(getdate()) ,
   [UpdateTime] datetime NULL DEFAULT(getdate()) ,
   CONSTRAINT[PK_JT_DM_CoalWashing] PRIMARY KEY([ID])
   )
   ON[PRIMARY]
   GO*/

            StringBuilder sbTable = new StringBuilder();
            sbTable.AppendLine(string.Format("IF EXISTS(SELECT * FROM sysobjects WHERE name = '{0}') ",TableName));
            sbTable.AppendLine(string.Format("Drop Table {0}", TableName));

            sbTable.AppendLine(string.Format("CREATE TABLE[dbo].[{0}]", TableName));
            sbTable.AppendLine("(");

            for (int i = 0; i < sQLGenerateFields.Count; i++)
            {
                SQLGenerateField item = sQLGenerateFields.ElementAt<SQLGenerateField>(i);
                string fieldname = item.Name;

                string fieldtype;
                if (string.IsNullOrWhiteSpace(item.Length))
                {
                    fieldtype = item.Type;
                }
                else
                {
                    fieldtype = String.Format("{0}({1})", item.Type, item.Length);
                }

                string fieldNull;

                if (item.IsKey || item.EnableNull == false)
                {
                    fieldNull = "NOT NULL";
                }
                else
                {
                    fieldNull = "NULL";
                }

                string comma = i == (sQLGenerateFields.Count - 1) ? "" : ",";


                sbTable.AppendLine(string.Format("[{0}] {1} {2}{3} ", fieldname, fieldtype, fieldNull,comma));

            }
            sbTable.AppendLine(")");


            //设置主键

            //            ALTER TABLE Skills DELETE PRIMARY KEY
            //ALTER TABLE Skills ADD PRIMARY KEY(SkillID, SkillName );

            var Keys = sQLGenerateFields.Where(x => x.IsKey == true).Select(x=>x.Name).ToList();

            if (Keys!=null)
            {
               string sqlkey = String.Format("ALTER TABLE {0} ADD PRIMARY KEY({1})", 
                   TableName,string.Join(",",Keys));
                sbTable.AppendLine(sqlkey);
            }


            // ALTER TABLE[dbo].[JT_DM_WeeklyReport] ADD DEFAULT(getdate()) FOR[CreateTime]
            //增加默认值功能

            var defaultValue = sQLGenerateFields.
                Where(x => x.DefaultValue!=null&& !string.IsNullOrWhiteSpace(x.DefaultValue))
                .ToList();



            if (defaultValue != null)
            {
                foreach (var item in defaultValue)
                {
                    string sqlkey = String.Format("ALTER TABLE {0} ADD DEFAULT({1}) FOR [{2}]",
                    TableName,item.DefaultValue,item.Name);
                    sbTable.AppendLine(sqlkey);
                }
            }



            //创建Sql备注功能
            /*

            IF((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'NewTable',
'COLUMN', N'StatisticsDate')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'统计日期'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'NewTable'
, @level2type = 'COLUMN', @level2name = N'StatisticsDate'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'统计日期'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'NewTable'
, @level2type = 'COLUMN', @level2name = N'StatisticsDate'
GO*/

            foreach (var item in sQLGenerateFields)
            {

                if (!string.IsNullOrWhiteSpace(item.Remark))
                {
                   var sqlRemark=  string.Format(@"
 IF((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'{0}',
'COLUMN', N'{2}')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'{1}'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'{0}'
, @level2type = 'COLUMN', @level2name = N'{2}'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'{1}'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'{0}'
, @level2type = 'COLUMN', @level2name = N'{2}'
GO",TableName,item.Remark,item.Name);
                    sbTable.AppendLine(sqlRemark);
                }

            }



                return sbTable.ToString();
        }

    }
}
